﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Bai2
{
        class SinhVienDAL
        {
            public SinhVienDAL()
            {

            }
            public DataTable DocDanhSachSinhVien()
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data Source=(local);Initial Catalog=qlsv; User Id=sa; Password=sa";
                conn.Open();
                SqlCommand cmd = new SqlCommand("select * from SinhVien", conn);
                SqlDataReader dr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                return dt;
            }
            public void XoaSinhVien(String Masv)
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data source=(local); initial catalog=qlsv; user id=sa;password=sa";
                conn.Open();
                SqlCommand cmd = new SqlCommand("delete from SinhVien where Masv='" + Masv + "'", conn);
                cmd.ExecuteNonQuery();
            }
            public void ThemSinhVien(string Masv, string Hosv, string Tensv,
                              string NgaySinh, string GioiTinh, string MaKhoa)
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data source=(local); initial catalog=qlsv; user id=sa;password=sa";
                conn.Open();
                SqlCommand cmd = new SqlCommand("insert into SinhVien values('" + Masv + "',N'" + Hosv + "',N'" + Tensv + "','" + NgaySinh + "','" + GioiTinh + "','" + MaKhoa + "')", conn);
                cmd.ExecuteNonQuery();
            }
            public void SuaSinhVien(string Masv, string Hosv, string Tensv,
                              string NgaySinh, string GioTinh, string MaKhoa)
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data source=(local); initial catalog=qlsv; user id=sa;password=sa";
                conn.Open();
                SqlCommand cmd = new SqlCommand("update SinhVien set Masv = '" + Masv + "', Hosv = '" + Hosv + "',Tensv='" + Tensv + "',NgaySinh='" + NgaySinh + "',GioTinh='" + GioTinh + "',Makhoa='" + MaKhoa + "' where masv ='" + Masv + "'", conn);
                cmd.ExecuteNonQuery();
            }
        
             
     }
}

